﻿using SharpSoft.Data;
using SharpSoft.Web;
using System;
using System.Collections.Generic;
using System.Text;

namespace SharpSoft.BL
{
    public class UserRepository : IUserRepository
    {
        private readonly Database db;
        public UserRepository(IDatabaseService dbser)
        {
            db = dbser.Database;
        }
        public void ChangePassword(string uname, string newpassword)
        {
            throw new NotImplementedException();
        }


        public void UpdateToken(int uid, string token)
        {
            string sql = "UPDATE Sys_Users SET Token=@token WHERE UserId=@id";
            db.ExecuteNonQuery(sql, new { token = token, id = uid });
        }

        public string CreateUser(IUser user)
        {
            string queryexists = @"SELECT [UserName] FROM Sys_Users WHERE UserName=@UserName";
            string insertsql = @"INSERT INTO Sys_Users(UserName,Password,MobilePhone,Email,Status,CreateTime)VALUES(@UserName, @Password, @MobilePhone, @Email,0,getdate());";

            if (db.ExecuteExists(new GSQL(queryexists), user))
            {
                throw new Exception("用户名已存在。");
            }
            //if (!string.IsNullOrEmpty(user.MobilePhone) && db.ExecuteExists("SELECT [UserName] FROM Sys_Users WHERE MobilePhone=@MobilePhone", user))
            //{
            //    throw new Exception("该手机号已被注册。");
            //}
            if (!string.IsNullOrEmpty(user.Email) && db.ExecuteExists("SELECT [UserName] FROM Sys_Users WHERE Email=@Email", user))
            {
                throw new Exception("该邮箱已被注册。");
            }
            db.ExecuteNonQuery(new GSQL(insertsql), user);
            var uid = db.ExecuteScalar("SELECT UserId FROM Sys_Users WHERE UserName=@UserName", user);
            return Convert.ToString(uid);
        }

        public bool ExistsUserName(string uname)
        {
            string sql = "SELECT * FROM Sys_Users WHERE UserName=@UserName";
            return db.ExecuteExists(sql, new { UserName = uname });
        }

        public string[] GetAllUser()
        {
            return db.ExecuteArray<string>("SELECT UserName FROM Sys_Users;");
        }
        /// <summary>
        /// 根据账户Id获取该账户所属的企业Id
        /// </summary>
        /// <returns></returns>
        public int? GetEnterpriseIdByUserId(int uid)
        {
            List<int> us = new List<int>();
            us.Add(uid);
            Action<List<int>, int[]> ac = null;
            ac = new Action<List<int>, int[]>((ls, uids) =>
            {
                ls.AddRange(uids);
                foreach (var item in uids)
                {
                    string sql = "SELECT UserId from Sys_UserRelations " +
                "WHERE OtherUserId=@uid and Relation=@relation";
                    var pusers = db.ExecuteArray<int>(sql, new { uid = item, relation = 1 });
                    ac(ls, pusers);
                }
            });
            ac(us, new int[] { uid });//递归查找关联父账户
            us.Reverse();
            foreach (var id in us)
            {
                string sql = "SELECT [Id] FROM [inf_Enterprise] WHERE [UserId]=" + id;
                var eid = db.ExecuteScalar<int?>((GSQL)sql);
                if (eid.HasValue)
                {
                    return eid.Value;
                }
            }
            return null;
        }
        public IUser GetUser(string username, string pwd)
        {
            string sql = "SELECT * FROM Sys_Users WHERE UserName=@uname and Password=@pwd;";
            var user = db.ExecuteObject<UserForMtps>(sql, new { uname = username, pwd = pwd });
            if (user == null)
            {
                return null;
            }
            int? eid = GetEnterpriseIdByUserId(user.UserId);
            int? companyid = null;
            if (eid.HasValue)
            {
                companyid = db.ExecuteScalar<int?>($"SELECT CompanyId FROM inf_enterprise WHERE Id={eid.Value}");
            }
            user.UserData = $"{eid},{companyid},{db.ExecuteExists($"SELECT Id FROM inf_enterprise WHERE UserId={user.UserId};")}";
            return user;
        }

        public IDictionary<PermissionSource, bool> GetUserPermissions(string uname)
        {
            var roles = GetUserRoles(uname);
            if (roles == null || roles.Length == 0)
            {
                return new Dictionary<PermissionSource, bool>();
            }
            string sql = $"SELECT * FROM Sys_PermissionsForRole WHERE RoleId in ({string.Join(",", roles)})";
            var array = db.ExecuteJArray(sql);
            Dictionary<PermissionSource, bool> dic = new Dictionary<PermissionSource, bool>();
            foreach (var item in array)
            {
                var allow = array["Allow"].ToObject<bool>();
                var key = new PermissionSource(array["ActionName"].ToString(), array["ControllerName"].ToString(), array["MoudleName"].ToString());
                if (dic.ContainsKey(key))
                {
                    dic[key] = allow || dic[key];
                }
                else
                {
                    dic.Add(key, allow);
                }
            }
            return dic;
        }
        public IDictionary<PermissionSource, bool> GetRolePermissions(string roleid)
        {
            string sql = $"SELECT * FROM Sys_PermissionsForRole WHERE RoleId={roleid}";
            var array = db.ExecuteJArray(sql);
            Dictionary<PermissionSource, bool> dic = new Dictionary<PermissionSource, bool>();
            foreach (var item in array)
            {
                var allow = array["Allow"].ToObject<bool>();
                var key = new PermissionSource(array["ActionName"].ToString(), array["ControllerName"].ToString(), array["MoudleName"].ToString());
                if (dic.ContainsKey(key))
                {
                    dic[key] = allow || dic[key];
                }
                else
                {
                    dic.Add(key, allow);
                }
            }
            return dic;
        }

        public bool MatchPassword(string uname, string password)
        {
            string sql = "SELECT * FROM Sys_Users WHERE UserName=@UserName and Password=@Password";
            return db.ExecuteExists(sql, new { UserName = uname, Password = password });
        }
        public string[] GetUserRoles(string uname)
        {
            string sql = @"SELECT a.RoleId FROM Sys_User_Role_Mapping AS a
JOIN Sys_Users AS b ON a.UserId=b.UserId
WHERE b.UserName=@uname;";
            int[] rs = db.ExecuteArray<int>(sql, new { uname = uname });
            string[] rss = new string[rs.Length];
            for (int i = 0; i < rs.Length; i++)
            {
                rss[i] = rs[i].ToString();
            }
            return rss;
        }
        public void SetUserRoles(string uname, string[] roles)
        {
            int uid = db.ExecuteInt32("SELECT UserId FROM Sys_Users WHERE UserName=@UserName", new { UserName = uname });
            string sql = "DELETE FROM Sys_User_Role_Mapping WHERE UserId=" + uid;
            string ins = "INSERT INTO Sys_User_Role_Mapping(UserId,RoleId)VALUES(@uid,@rid);";
            if (roles != null)
            {
                foreach (var item in roles)
                {
                    db.ExecuteNonQuery(ins, new { uid = uid, rid = int.Parse(item) });
                }
            }

        }

        public void SetUserStatus(string uname, int status)
        {
            string sql = "UPDATE Sys_Users SET Status=@status WHERE UserName=@uname";
            db.ExecuteNonQuery(sql, new { uname = uname, status = status });
        }
        public string[] GetRelationUsers(string username, int relation)
        {
            string sql = "SELECT UserName FROM Sys_Users WHERE UserId IN( SELECT OtherUserId from Sys_UserRelations as a " +
                "ON Sys_Users as b on a.UserId=b.UserId " +
                "WHERE b.UserName=@uname and a.Relation=@relation)";
            return db.ExecuteArray<string>(sql, new { uname = username, relation = relation });
        }

        public string GetUserId(string uname)
        {
            var uid = db.ExecuteScalar("SELECT UserId From Sys_Users WHERE UserName=@uname", new { uname = uname });
            return Convert.ToString(uid);
        }

        public void SetUserRelation(string username, string otherusername, int relation)
        {
            string uid = GetUserId(username);
            string uido = GetUserId(otherusername);
            object OBJ = new { relation = relation, uid = uid, uido = uido };
            if (db.ExecuteExists("SELECT * FROM Sys_UserRelations WHERE UserId=@uid AND OtherUserId=@uido", OBJ))
            {
                db.ExecuteNonQuery("UPDATE Sys_UserRelations SET Relation=@relation WHERE UserId=@uid AND OtherUserId=@uido", OBJ);
            }
            else
            {
                db.ExecuteNonQuery("INSERT INTO Sys_UserRelations(UserId,OtherUserId,Relation)VALUES(@uid,@uido,@relation)", OBJ);
            }
        }
        public void UpdateUserInfo(IUser uinfo)
        {
            throw new NotImplementedException();
        }

    }
}
